<?
include "../db_config.php";
include "Database.class.php";
include_once "snoopy/Snoopy.class.php";

$db = new Database($host_name, $user_name, $db_password, $db_name);
$db->connect();

echo "사용금지";
exit(0);

function findstr(&$source, $startkey, $endkey, &$offset)
{
	$s = strpos($source, $startkey, $offset)+strlen($startkey);
	$e = strpos($source, $endkey, $s);
	//$str = substr($source, $s, $e-$s-strlen($endkey)+1);
	$str = substr($source, $s, $e-$s);
	$offset = $e + strlen($endkey)-1;
	return $str;
}

//다른 URL에 접속하여, 정확한 도착시각과 출발시각을 찾아낸다
function findDetailTime($url)
{
	$snoopy = new Snoopy;
	$snoopy->fetch($url);
	$source = iconv('euc-kr', 'utf-8', $snoopy->results);
	
	$key = "<td ><b>";
	$end = "</b></td>";
	$offset = 0;

	$data = array();
	
	$i=0;
	while(strpos($source, $key, $offset))
	{
		$d = findstr($source, $key, $end, $offset);
		$data[$i++] = $d;
	}
	//print_r($data);
	return $data;
}
function findStationInfo($source)
{
	global $db;
	$offset = 0;
	$key = "] = new Array(";
	$end = ");";
	while(strpos($source, $key, $offset))
	{
		$d = findstr($source, $key, $end, $offset);
		$d = str_replace("\"", "", $d);

		$t = split(",", $d);
		$data = array();
		$data['stationId'] = trim($t[2]);
		$data['stationName'] = trim($t[0]);
		$data['line'] = trim($t[1]);

		$db->query_insert("stationInfo", $data);
		print_r($data);
		echo "<BR>\n";

			echo "$d<BR>\n";
	}
}
function updateTrainInfo()
{

	global $db;
	$sql = "select distinct updown, trainId, line from stationTimeTable";
	$rows = $db->fetch_all_array($sql);
	foreach($rows as $data)
	{
		for($i = 1; $i <= 3; $i++)
		{
			$sql = "select min(departTime), max(departTime) from
			stationTimeTable where "
			. "trainId = '" . $data[trainId] . 
			"' and line = '" . $data[line] . "'" .
			" and week = '$i'";
			$t = $db->query_first($sql);
			$data['startTime'.$i] = $t['min(departTime)'];
			$data['endTime'.$i] = $t['max(departTime)'];
		}
		$db->query_insert("trainInfo", $data);
	}
	echo "updateTrainInfo 완료";
}

//정확한 시간표 업데이트
function updateDetailedTimeTable()
{
	global $db;
	$sql = "select line, stationId, stationName from stationInfo";
	$rawdata = $db->fetch_all_array($sql);
	$stations = array();
	foreach($rawdata as $t)
	{
		$stations[$t[line].$t[stationName]] = $t[stationId];
	}

	$snoopy = new Snoopy;

	$update_data = array();

	$sql = "select * from trainInfo where tid > 3563 order by tid";
	$rows = $db->fetch_all_array($sql);
	foreach($rows as $d)
	{
		$updown = $d[updown];
		$line = $d[line];
		$trainId = $d[trainId];

		$startend = array();

		for($i = 1; $i <= 3; $i++)
		{
			if($d['startTime'.$i] != null)
			{
				$week = $i;
					
				$sql = "select stationId from stationTimeTable where week = '$week'
				and updown = '$updown' and line = '$line' and trainId like
				'$trainId' limit 1";
				$t = $db->query_first($sql);
				$stationId = $t[stationId];

				$url =
				"http://seoulmetro.co.kr/station/stationtrainschedule.action?eng=&".
				"stTrainNo=$trainId&mainLine=$line&inoutTag=$updown&weekTag=$week".
				"&stationId=$stationId";
				//echo "<a href=$url>$url</a><BR>\n";
				$snoopy->fetch($url);
				$source = iconv('euc-kr', 'utf-8', $snoopy->results);

				$offset = 0;
$key = "class=\"form_schedule_left\">  
	<td ><font color='black'>";
				$end = "</b></font>";
				while(strpos($source, $key, $offset))
				{
					$key2 = "<td >";
					$end2 = "</b>";
					$sname = trim(findstr($source, $key, $end, $offset));
					$start_time = trim(findstr($source, $key2, $end2, $offset));
					$end_time = trim(findstr($source, $key2, $end2, $offset));
					$sname = str_replace("<b>", "", $sname);
					$start_time = str_replace("<b>", "", $start_time);
					$end_time = str_replace("<b>", "", $end_time);

					$sid = $stations[$line.$sname];
					//echo "$sname ($sid) $start_time $end_time <BR>\n";

					$update_data['arrivalTime'] = $start_time;
					$update_data['departTime'] = $end_time;
					if($sid == null)
					{
						echo "cannot find $sname <BR>\n";
						continue;
					}
					$where = "stationId='$sid' and updown='$updown' and
					week = '$week' and trainId = '$trainId'";
					$db->query_update('stationTimeTable', $update_data,	$where);

					if($startend['startTime'.$i] == null)
						$startend['startTime'.$i] = $end_time;
					$startend['endTime'.$i] = $start_time;
				}
			}
		}
		$where = "trainId = '$trainId' and line = '$line'";
		$db->query_update('trainInfo', $startend, $where);
		//print_r($startend);
		echo $d[tid] . " $trainId 완료<BR>\n";
		flush();
	}
	echo "detail 시간표 업데이트 완료";
}

function updateStationPos()
{
	global $db;
	$sql = "select * from stationInfo";
	$rows = $db->fetch_all_array($sql);
	$mapapi =
	"VGP6t6zV34GzKTMdb5a6zb17MbIJi8.DqwrwG12yHjFx_kKi1qRk36EFwatx";
	$snoopy = new Snoopy;
	$baseurl =
		"http://kr.open.gugi.yahoo.com/service/poi.php?appid=$mapapi&output=json&results=50";
	foreach($rows as $st)
	{
		$stationId = $st[stationId];
		$stationName = $st[stationName] . "역";
		$url = $baseurl ."&q=$stationName";
		$snoopy->fetch($url);
		$result = json_decode($snoopy->results);
		$t = $result->ResultSet->locations->item[0];

		//검색된 결과가 역 이름이 아니면 무시
		if($stationName != $t->name)
		{
			echo "$stationName != " . $t->name . "<BR>\n";
			flush();
			continue;
		}
		$data = array();
		$data[lat] = $t->latitude;
		$data[lng] = $t->longitude;
		$db->query_update("stationInfo", $data, "stationId='$stationId'");
		//print_r($data);
		//echo "<BR>";
	}
	echo "역 위치 정보 업데이트 완료";
}

function findScheduleData($baseurl)
{
	global $db;

	$snoopy = new Snoopy;
	for($i = 1; $i <= 3; $i++)
	{
		$url = $baseurl ."&weekTag=$i";
		$snoopy->fetch($url);
		$source = iconv('euc-kr', 'utf-8', $snoopy->results);
		$offset = 0;
		$key = "javascript:showtrainschedule(";
		$end = ")\">";
		while(strpos($source, $key, $offset))
		{
			$d = findstr($source, $key, $end, $offset);
			//bug??
			$d = substr($d, 0, -3);

			$d = str_replace("'", "", $d);
			$t = split(",", $d);
			$data = array();
			$data['stationId'] = trim($t[4]);
			$data['line'] = trim($t[1]);
			$data['updown'] = trim($t[2]);
			$data['week'] = trim($t[3]);
			$data['trainId'] = trim($t[0]);
			$data['departTime'] = trim($t[5]);

			$url =
			"http://www.seoulsubway.co.kr/station/stationtrainschedule.action?eng=".
			"&stTrainNo=".$data[trainId]. 
			"&mainLine=".$data[line].
			"&inoutTag=".$data[updown].
			"&weekTag=".$data[week].
			"&stationId=".$data[stationId];

			//시간이 너무 많이 걸린다
			//$time = findDetailTime($url);
			//$data['arrivalTime'] = $time[0];
			//$data['departTime'] = $time[1];

			$db->query_insert("stationTimeTable", $data);
			//print_r($data);
			//echo "<BR>\n";
		}
	}
}



$url =
"http://www.seoulsubway.co.kr/station/trainschedule.action?eng=&searchValue=&stationId=0330&mainLine=3";

$snoopy = new Snoopy;
$snoopy->fetch($url);
$source = iconv('euc-kr', 'utf-8', $snoopy->results);


//초기 station 정보를 얻음
//DB에 넣고 나면 호출할 필요 없음
//findStationInfo($source);


//역 위치 정보 추가
//updateStationPos();

//아래 함수들은 DB에 역정보가 있을 때 실행 가능
//한 번에 100개 씩 입력하는 것을 추천함 ㅡ,.ㅡ
//$sql = "select * from stationInfo limit 151, 1000";
/*
$sql = "select * from stationInfo limit 341, 1000";
$rows = $db->fetch_all_array($sql);
foreach($rows as $data)
{
	$url =
	"http://www.seoulsubway.co.kr/station/trainschedule.action?eng=&searchValue=".
	"&stationId=". $data[stationId] .
	"&mainLine=" . $data[line];

	findScheduleData($url);
	echo $data[stationName]. "역 정보 수집 완료<BR>\n";
	flush();
	sleep(5);
}
echo "완료<BR>\n";
*/


//stationTimeTable이 완성된 다음에 아래 함수 실행
//updateTrainInfo();

//정확한 열차 시간표 입력
updateDetailedTimeTable();

$db->close();
exit(0);

$display_no = 0;





parse_file('data/right.txt');
parse_file('data/left.txt');
parse_file('data/mapotosangsu.txt');
parse_file('data/sangsutohome.txt');
parse_file('data/sangamtomapo.txt');
parse_file('data/04011.txt');
parse_file('data/04012.txt');
parse_file('data/04013.txt');
parse_file('data/test.txt');

parse_file('data/040801');
parse_file('data/040802');
parse_file('data/040803');
parse_file('data/040804');
parse_file('data/040805');
parse_file('data/040806');

parse_file('data/090422_1.txt');
parse_file('data/090422_2.txt');

//parse_file('data/20090415.txt');

function make_sql($tableName, $item, $str)
{
	$sql = "insert into $tableName (";
	$del = "";
	foreach($item as $key => $val)
	{
		$sql = $sql . $del .$key;
		if($del == "")
			$del = ", ";
	}
	foreach($str as $key => $val)
	{
		$sql = $sql . $del .$key;
		if($del == "")
			$del = ", ";
	}
	$sql .= ") values(";
	$del = "";
	foreach($item as $key => $val)
	{
		$sql = $sql . $del .$val;
		if($del == "")
			$del = ", ";
	}
	foreach($str as $key => $val)
	{
		$sql = $sql . $del ."'$val'";
		if($del == "")
			$del = ", ";
	}
	$sql .=");";

	return $sql;
}

function parse_file($fname)
{
	echo "<br>$fname<br>\n";
	//$lines = file('right.txt');
	$lines = file($fname);
	$i = 0; 
	foreach($lines as $line_no => $line)
	{
		//주석은 무시
		if(substr($line, 0, 1) == '#')
			continue;
		$data = split('	', $line);
		$item = array();
		$str = array();
		$item['Latitude'] = substr($data[0], 2);
		$item['Longitude'] = substr($data[1], 2);
		$str['Pos'] = $item['Latitude'] . " " . $item['Longitude'];
		$str['SSID'] = trim($data[2], "() ");
		$str['BSSID'] = trim($data[4], "() ");
		$str['Time'] = $data[5];
		$str['logfile'] = $fname; 
		$signals = split(' ', $data[6]);
		$item['SNR'] = $signals[1];
		$item['Sig'] = $signals[2];
		$item['Noise'] = $signals[3];
		$item['Flags'] = $data[8];
		$item['Channelbits'] = $data[9];
		$item['BcnIntvl'] = $data[10];
		$item['DataRate'] = $data[11];
		$item['LastChannel'] = $data[12];

		if($item['Latitude'] == 0 || $item['Longitude']==0)
			continue;
		if($item['SNR'] == 0)
			continue;

		//raw data에 추가
		$sql = make_sql("ap_raw_data", $item, $str);
		mysql_query($sql);

		$sql = "select COUNT(*) from ap_raw_data where BSSID like '".$str['BSSID']."'";
		$result = mysql_query($sql);
		$data = mysql_fetch_array($result);
		$sourceNum = $data['COUNT(*)'];

		$item['sourceNum'] = $sourceNum;

		$sql = "select * from ap_data where BSSID like '".$str['BSSID']."'";
		$result = mysql_query($sql);
		$data = mysql_fetch_array($result);



		$sql = make_sql("ap_data", $item, $str);


		//기존에 DB에 값이 없으면
		if($data == NULL)
		{
			//DB에 바로 추가
			$result = mysql_query($sql);
		}
		//이미 존재하는 경우 
		else
		{
			$obj = $data; 
			//SNR이 더 큰 경우는 교체
			if($obj['SNR'] < $item['SNR'])
			{
				$sql = "update ap_data set ";
				$del = "";
				foreach($item as $key => $val)
				{
					$sql = $sql . $del .$key . "=". $val;
					if($del == "")
						$del = ", ";
				}
				foreach($str as $key => $val)
				{
					$sql = $sql . $del .$key . "='".$val."'";
					if($del == "")
						$del = ", ";
				}
				$sql .= " where BSSID = '".$obj['BSSID']."'";
				//$tsql = "delete from ap_data where BSSID='".$item['BSSID']."';";
				//mysql_query($tsql);
				//echo $sql . "<br>\n";
				mysql_query($sql);
			}
		}

		//echo "$i <br>\n";
		//flush();

		$i++;

		//echo "line $line_no : $line<br>\n";
	}
	echo "total : $i points are updated<br>\n";
	flush();
}
?>
